package dao;



import entity.UserInfo;
import utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserInfoDao implements BaseDao<UserInfo> {
    @Override
    public boolean insert(UserInfo userInfo) throws SQLException, ClassNotFoundException {
        Connection connection = JDBCUtils.openConn();
        boolean flag = false;
        String sql = "insert into user_info (user_name,user_pwd,user_age) values (?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,userInfo.getUserName());
        statement.setString(2,userInfo.getUserPwd());
        statement.setInt(3,userInfo.getUserAge());
        //返回值为sql影响的行数
        int count = statement.executeUpdate();
        if(count>0){
            flag = true;
        }
        statement.close();
        connection.close();
        return flag;
    }

    @Override
    public boolean deleteById(int id) throws SQLException, ClassNotFoundException {
        Connection connection = JDBCUtils.openConn();
        boolean flag = false;
        String sql = "delete from user_info where user_id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,id);
        //返回值为sql影响的行数
        int count = statement.executeUpdate();
        if(count>0){
            flag = true;
        }
        statement.close();
        connection.close();
        return flag;
    }

    @Override
    public boolean update(UserInfo userInfo) throws SQLException, ClassNotFoundException {
        Connection connection = JDBCUtils.openConn();
        boolean flag = false;
        String sql = "update user_info set user_name = ?,user_pwd = ?, user_age = ? where user_id = ?";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,userInfo.getUserName());
        statement.setString(2,userInfo.getUserPwd());
        statement.setInt(3,userInfo.getUserAge());
        statement.setInt(4,userInfo.getUserId());
        //返回值为sql影响的行数
        int count = statement.executeUpdate();
        if(count>0){
            flag = true;
        }
        statement.close();
        connection.close();
        return flag;
    }

    @Override
    public List<UserInfo> selectAll() throws SQLException, ClassNotFoundException {
        Connection connection = JDBCUtils.openConn();

        List<UserInfo> userInfoList = new ArrayList<>();

        //操作数据库的sql语句
        String sql = "select * from user_info";
        //操作数据库的对象
        PreparedStatement statement = connection.prepareStatement(sql);
        //执行sql语句
        ResultSet resultSet = statement.executeQuery();
        //遍历结果集
        while(resultSet.next()){
            Integer userId = resultSet.getInt("user_id");
            String userName = resultSet.getString("user_name");
            String userPwd = resultSet.getString("user_pwd");
            Integer userAge = resultSet.getInt("user_age");

            UserInfo userInfo = new UserInfo(userId,userName,userPwd,userAge);
            userInfoList.add(userInfo);
        }

        System.out.println(userInfoList);

        resultSet.close();
        //资源释放
        statement.close();
        connection.close();
        return userInfoList;
    }

    @Override
    public UserInfo selectById(int id) throws SQLException, ClassNotFoundException {
        Connection connection = JDBCUtils.openConn();

        // ?是占位符 表示将要在此处填充参数
        String sql = "select * from user_info where user_id = ?";

        UserInfo userInfo = null;
        //操作数据库的对象
        //Statement statement = connection.createStatement();
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1,id);
        //执行sql语句
        ResultSet resultSet = statement.executeQuery();
        //遍历结果集
        while(resultSet.next()){
            Integer userId = resultSet.getInt("user_id");
            String thisUserName = resultSet.getString("user_name");
            String thisUserPwd = resultSet.getString("user_pwd");
            Integer userAge = resultSet.getInt("user_age");

            userInfo = new UserInfo(userId,thisUserName,thisUserPwd,userAge);

        }

        resultSet.close();
        //资源释放
        statement.close();
        connection.close();
        return userInfo;
    }

    public UserInfo login(String userName,String userPwd) throws SQLException, ClassNotFoundException {
        Connection connection = JDBCUtils.openConn();

        // ?是占位符 表示将要在此处填充参数
        String sql = "select * from user_info where user_name  = ? and user_pwd = ?";

        UserInfo userInfo = null;
        //操作数据库的对象
        //Statement statement = connection.createStatement();
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,userName);
        statement.setString(2,userPwd);
        //执行sql语句
        ResultSet resultSet = statement.executeQuery();
        //遍历结果集
        while(resultSet.next()){
            Integer userId = resultSet.getInt("user_id");
            String thisUserName = resultSet.getString("user_name");
            String thisUserPwd = resultSet.getString("user_pwd");
            Integer userAge = resultSet.getInt("user_age");

             userInfo = new UserInfo(userId,thisUserName,thisUserPwd,userAge);

        }



        resultSet.close();
        //资源释放
        statement.close();
        connection.close();
        return userInfo;
    }

    @Override
    public int batchInsert(List<UserInfo> userInfos) throws SQLException, ClassNotFoundException {
        Connection connection = JDBCUtils.openConn();
        String sql = "insert into user_info (user_name,user_pwd,user_age) values (?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        for(UserInfo userInfo:userInfos) {
            statement.setString(1, userInfo.getUserName());
            statement.setString(2, userInfo.getUserPwd());
            statement.setInt(3, userInfo.getUserAge());
            statement.addBatch();
        }
        int[] arr = statement.executeBatch();
        statement.close();
        connection.close();
        return arr.length;
    }
}
